package cn.steel.utils;

import cn.steel.domain.entity.Product;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {

    // 导入Excel文件并解析为商品列表
    public static List<Product> parseExcel(MultipartFile file) throws IOException {
        List<Product> products = new ArrayList<>();
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        // 跳过表头，从第二行开始读取
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            Product product = new Product();
            product.setName(getStringCellValue(row.getCell(0)));
            product.setCategoryId(getLongCellValue(row.getCell(1)));
            product.setSpecification(getStringCellValue(row.getCell(2)));
            product.setMaterial(getStringCellValue(row.getCell(3)));
            product.setInventory(getIntegerCellValue(row.getCell(4)));
            product.setPrice(new BigDecimal(getStringCellValue(row.getCell(5))));
            product.setShippingAddress(getStringCellValue(row.getCell(6)));
            product.setShippingRegion(getStringCellValue(row.getCell(7)));
            product.setMaterialReportUrl(null); // 显式设置为 null
            product.setImageUrl(null); // 显式设置为 null
            product.setCreateTime(LocalDateTime.now());
            product.setUpdateTime(LocalDateTime.now());
            product.setStatus(0); // 默认下架

            products.add(product);
        }
        workbook.close();
        return products;
    }

    // 导出商品数据到Excel
    public static byte[] exportExcel(List<Product> products, List<String> fields) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Products");
        Row headerRow = sheet.createRow(0);

        // 定义表头
        List<String> headers = List.of("商品名称", "分类ID", "规格", "材质", "库存", "价格", "发货地址", "发货区域");
        List<Integer> fieldIndices = new ArrayList<>();
        for (int i = 0; i < headers.size(); i++) {
            if (fields.contains(headers.get(i))) {
                Cell cell = headerRow.createCell(fieldIndices.size());
                cell.setCellValue(headers.get(i));
                fieldIndices.add(i);
            }
        }

        // 填充数据
        int rowNum = 1;
        for (Product product : products) {
            Row row = sheet.createRow(rowNum++);
            int cellIndex = 0;
            for (int index : fieldIndices) {
                Cell cell = row.createCell(cellIndex++);
                switch (index) {
                    case 0: cell.setCellValue(product.getName()); break;
                    case 1: cell.setCellValue(product.getCategoryId()); break;
                    case 2: cell.setCellValue(product.getSpecification()); break;
                    case 3: cell.setCellValue(product.getMaterial()); break;
                    case 4: cell.setCellValue(product.getInventory()); break;
                    case 5: cell.setCellValue(product.getPrice().toString()); break;
                    case 6: cell.setCellValue(product.getShippingAddress()); break;
                    case 7: cell.setCellValue(product.getShippingRegion()); break;
                }
            }
        }

        // 自动调整列宽
        for (int i = 0; i < fieldIndices.size(); i++) {
            sheet.autoSizeColumn(i);
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();
        return outputStream.toByteArray();
    }

    private static String getStringCellValue(Cell cell) {
        if (cell == null) return "";
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }

    private static Long getLongCellValue(Cell cell) {
        if (cell == null) return null;
        cell.setCellType(CellType.NUMERIC);
        return (long) cell.getNumericCellValue();
    }

    private static Integer getIntegerCellValue(Cell cell) {
        if (cell == null) return null;
        cell.setCellType(CellType.NUMERIC);
        return (int) cell.getNumericCellValue();
    }
}